package com.changil.nowonchangil.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.PreparedStatement;

import com.changil.nowonchangil.dto.BibleWord;

public class ThisWeekBibleWordManager extends BaseTableManager{
	private static ThisWeekBibleWordManager instance = null;
	final static String TABLE_NAME = "Article";
	static enum  COLUMNS {
		id,
		boardId,
		title,
		content,
		userId,
		writtenDateTime
	}
	
	public static ThisWeekBibleWordManager getsInstance() {
		if (instance == null) {
			synchronized (ThisWeekBibleWordManager.class) {
				if (instance == null) { 
					instance = new ThisWeekBibleWordManager();
				}
			}
		}
		return instance;
	}
	
	
	/**
	 * @param date yyyymmdd
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<BibleWord> select(String date) {
		String sql = "SELECT * FROM " + ThisWeekBibleWordTableInfo.NAME
				+" WHERE "+ ThisWeekBibleWordTableInfo.FIELD_NAME_DATE +"= ?";
		Connection conn = createConnection();
		PreparedStatement statement = null;
		ResultSet result = null;
		ArrayList<BibleWord> list = new ArrayList<BibleWord>(); 
		try {
			statement = conn.prepareStatement(sql);
			statement.setString(1, date);
			result = statement.executeQuery();
			
			while (result.next()){
				list = BibleWordManager.getsInstance().select(
						result.getString(result.findColumn(ThisWeekBibleWordTableInfo.FIELD_NAME_BOOK)),
						result.getInt(result.findColumn(ThisWeekBibleWordTableInfo.FIELD_NAME_FROM_CHAPTER)), 
						result.getInt(result.findColumn(ThisWeekBibleWordTableInfo.FIELD_NAME_FROM_VERSE)),
						result.getInt(result.findColumn(ThisWeekBibleWordTableInfo.FIELD_NAME_TO_CHAPTER)), 
						result.getInt(result.findColumn(ThisWeekBibleWordTableInfo.FIELD_NAME_TO_VERSE)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(null != result) {
				try {
					result.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		return list;
	}
	
//	public void insert(String content) throws SQLException{
//		String sql = "INSERT INTO "
//					+HistoryTableInfo.NAME
//					+"("+HistoryTableInfo.FIELD_NAME_CONTENT+")"
//					+"VALUES (?)";
//		
//		PreparedStatement statement = DbConnectManager.getsInstance().getConnection().prepareStatement(sql);
//		statement.setString(1, content);
//		int rowsInserted = statement.executeUpdate();
//		if (rowsInserted > 0) {
//		    System.out.println("A new user was inserted successfully!");
//		}
//	}
//	
//	public void update(int targetId, String newContent) throws SQLException {
//		String sql = "UPDATE "
//					+HistoryTableInfo.NAME
//					+" SET "
//					+HistoryTableInfo.FIELD_NAME_CONTENT+"=? "
//					+"WHERE "
//					+HistoryTableInfo.FIELD_NAME_ID+"=?";
//		 
//		PreparedStatement statement = DbConnectManager.getsInstance().getConnection().prepareStatement(sql);
//		statement.setString(1, newContent);
//		statement.setInt(2, targetId);
//		int rowsUpdated = statement.executeUpdate();
//		if (rowsUpdated > 0) {
//		    System.out.println("An existing user was updated successfully!");
//		}
//	}
//	
//	public void delete(int targetId) throws SQLException{
//		String sql = "DELETE FROM "
//					+HistoryTableInfo.NAME
//					+" WHERE "
//					+HistoryTableInfo.FIELD_NAME_ID+"=?";
//		 
//		PreparedStatement statement = DbConnectManager.getsInstance().getConnection().prepareStatement(sql);
//		statement.setInt(1, targetId);
//		 
//		int rowsDeleted = statement.executeUpdate();
//		if (rowsDeleted > 0) {
//		    System.out.println("A user was deleted successfully!");
//		}
//	}
}
